package com.ml.springbootidus.file;


import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;


public class ReadExcel {

    static SimpleDateFormat simp =  new SimpleDateFormat("YYYY/MM/dd HH:mm:ss");

    public static void main(String[] args) throws IOException, InvalidFormatException, InvalidFormatException {


        String path = "C:\\Users\\zs\\Desktop\\代码\\原数据";        //要遍历的路径
        File files = new File(path);        //获取其file对象
        File[] fs = files.listFiles();    //遍历path下的文件和目录，放在File数组中
        for (File f : fs) {                    //遍历File[]数组
            if (!f.isDirectory())        //若非目录(即文件)，则打印
                System.out.println(f.getPath());
            File file = new File(f.getPath());//获取文件对象
            // }

            //File file = new File("C:\\Users\\Dream-Seeker\\Desktop\\导出数据2020-07-17\\(虹32)东体育会路DN500(瞬时流量).xlsx");//获取文件对象
            String[] split = file.getName().split("\\.");
            String fileName = split[0];//文件名称
            String filePath = "C:\\Users\\zs\\Desktop\\代码\\导出数据\\" + file.getName();


            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);//XSSF工作簿
            Sheet sheet = xssfWorkbook.getSheetAt(0); //默认取第一个sheet【获取工作表】
            int rowsNum = sheet.getLastRowNum();//获取最后一行数字
            List<Map<Integer, String>> mapList = new ArrayList<Map<Integer, String>>();//全部数据集合
            List<String> ExcepList = new ArrayList<String>();//存储异常信息的集合

            //            Map<Integer, Cell> map = new HashMap<Integer, Cell>(); //单个行数据集合
//            Cell storage = null;//暂存上一个索引为0的值
//            Integer point = 0;
//            String format001 = "";
            for (int i = 0; i < rowsNum ; i++) {
                //第一行
                Row row = sheet.getRow(i);
                //下一行
                Row Lastrow = sheet.getRow(i+1);

                //若行为空，则遍历下一行
                if (row == null) {
                    ExcepList.add("第"+(i+1)+"行的数据存在空行");
                    continue;
                }else{
                    if(i == rowsNum && Lastrow == null ){ExcepList.add("最后一行");}
                    Map<Integer, String> tamp = new HashMap<Integer, String>();
                    Cell cell = row.getCell(0);//获取指定单元格，单元格从左到右下标从0开始
                    Cell cell2 = row.getCell(1);
                    Cell Lastcell = Lastrow.getCell(0);//获取指定单元格的下一行
                    Cell Lastcel2 = Lastrow.getCell(1);
                    if(cell == null || cell2 == null ||Lastcell == null || Lastcel2 == null){
                        ExcepList.add("第"+(i+1)+"行或第"+(i+2)+"行的数据存在空单元格");
                    }else{
                        try{
                            double dataValue = cell.getNumericCellValue();
                            Date dateTime = cell2.getDateCellValue();
                            Date LastDateTime = Lastcel2.getDateCellValue();
                            Integer temp = processingTime(dateTime,LastDateTime);

                            tamp.put(0,dataValue+"");
                            tamp.put(1,simp.format(dateTime));
                            mapList.add(tamp);

                            for (int j= 1 ;j < temp ; j++){
                                tamp = new HashMap<Integer, String>();
                                tamp.put(0,dataValue+"");
                                tamp.put(1,processingTime2(dateTime,j));
                                mapList.add(tamp);
                            }

                        }catch (Exception e){
                            ExcepList.add("第"+(i+1)+"行和第"+(i+2)+"行的数据发生异常");
                        }

                    }
                }

//                for (int k = 0; k < row.getLastCellNum(); k++) {// getLastCellNum，是获取最后一个不为空的列是第几个






//                    if (cell != null && cell.getColumnIndex() == 0) {
//
//                        if (map != null && map.containsKey(0)) {
//                            storage = map.get(0);
//                        }
//                        map.put(0, cell);//储存当前索引的值，为添加顺延该值
//                        tamp.put(0, cell.toString());
//                    } else
//                if (cell != null && cell.getColumnIndex() == 1) {
//                        if (map != null && map.containsKey(1)) {
//                            Cell cell1 = map.get(1);

//                            //时间的格式转换
//                            long time1 = cell1.getDateCellValue().getTime();//上一个索引为1的日期
//                            long time = cell.getDateCellValue().getTime();//
//                            Integer Time = processingTime(time1, time);
//
//                            if (Time > 1) {//满足条件选择需要添加单元格【具体添加几个需要看差值】
//                                for (int j = 1; j < Time; j++) {
//                                    //添加测试
//                                    //1.索引为0的值
//                                    //System.out.println(storage);
//                                    //2.索引为1的值【日期】
//                                    time1 = time1 + 60000;
//                                    //毫秒转日期
//                                    Date date01 = new Date();
//                                    date01.setTime(time1);
//                                    String format01 = new SimpleDateFormat("YYYY/MM/dd HH:mm:ss").format(date01);
//                                    date01.setTime(time);
//                                    format001 = new SimpleDateFormat("YYYY/MM/dd HH:mm:ss").format(date01);
//                                    //System.out.println(format01);
//                                    Map<Integer, String> mapData = new HashMap<Integer, String>();
//                                    mapData.put(0, storage.toString());
//                                    mapData.put(1, format01);
//
//                                    mapList.add(mapData);
//                                    //计数
//                                    point++;
//                                }
//                            }
//
//
//                        }
//                        map.put(1, cell);//储存当前索引的值，为判断时间时候插入值
//                        tamp.put(1, format001);
//
//                        mapList.add(tamp);
                    }
                    creatXXLS(mapList, filePath, fileName);
                    for (String str:ExcepList) {
                        System.out.println(str);
                    }
                }
            }
//            System.out.println("总共添加了" + point + "条数据");



    /**
     * 时间处理
     *
     * @param Time
     * @param LastTime
     * @return
     */
    public static Integer processingTime(Date Time, Date LastTime) {
        String format1 = new SimpleDateFormat("mm").format(Time);
        String format = new SimpleDateFormat("mm").format(LastTime);
        Integer small = Integer.parseInt(format1);
        Integer big = Integer.parseInt(format);
        Integer Times = 0;
        //若下一值为 00分
        if(big > small)Times = big - small;
        else if(big < small)Times = big + (60-small);
        return Times;
    }
    //下一分钟时间
    public static String processingTime2(Date Time,Integer temp) {
        Long xs =Time.getTime();
        Date date01 = new Date();
        date01.setTime(xs+(temp*60000));
        return simp.format(date01);
    }


    /**
     * 创建Excel表格【写入】
     *
     * @param mapList
     * @param filePath
     */
    public static void creatXXLS(List<Map<Integer, String>> mapList, String filePath, String fileName) {
        //创建工作簿对象
        XSSFWorkbook workbook = new XSSFWorkbook();
        //为第一个sheet赋值
        XSSFSheet sheet = workbook.createSheet(fileName);
        for (int i = 0; i < mapList.size(); i++) {
            XSSFRow row = sheet.createRow(i);
            Map<Integer, String> map = mapList.get(i);
            XSSFCell cell = row.createCell(0); //第一列
            cell.setCellValue(map.get(0));
            cell = row.createCell(1); //第二列
            cell.setCellValue(map.get(1));

        }
        OutputStream out = null;
        try {
            out = new FileOutputStream(filePath);
            workbook.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
//
//    @Test
//    public void fileArr() {
//        String path = "C:\\Users\\Dream-Seeker\\Desktop\\导出数据2020-07-17";        //要遍历的路径
//        File file = new File(path);        //获取其file对象
//        File[] fs = file.listFiles();    //遍历path下的文件和目录，放在File数组中
//        for (File f : fs) {                    //遍历File[]数组
//            if (!f.isDirectory())        //若非目录(即文件)，则打印
//                System.out.println(f.getPath());
//            File files = new File(f.getPath());//获取文件对象
//        }
//    }

}